package com.yc.snack.dao.util;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 1、导包 -> WEB-INF/lib
 * 2、加载驱动
 * 3、建立连接
 * 4、创建指定语句块或预编译直接语句块
 * 5、给预编译执行语句块中的占位符赋值
 * 6、执行语句获取结果或结果集
 * 7、处理结果或结果集
 * 8、关闭资源
 * 数据查询工具
 * @author navy
 * Company 源辰信息
 * @date 2022年9月21日
 * Eamil haijunzhou@hnit.edu.cn
 */
public class DBUtil {
	static {
		try {
			Class.forName(ReadConfig.getIntance().getProperty("driver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取连接的
	 * @return
	 */
	private Connection getConnection() {
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(ReadConfig.getIntance().getProperty("url"), ReadConfig.getIntance());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	
	/**
	 * 关闭资源的方法
	 * @param rs
	 * @param pstmt
	 */
	private void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 给预编译执行语句块中的占位符赋值
	 * @param pstmt
	 * @param params
	 */
	private void setParams(PreparedStatement pstmt, Object ... params) {
		if (params == null || params.length <= 0) {
			return;
		}
		
		for (int i = 0, len = params.length; i < len; ++ i) {
			try {
				pstmt.setObject(i + 1, params[i]);
			} catch (SQLException e) {
				e.printStackTrace();
				System.out.println("第 " + (i + 1) + " 个参数注值失败...");
			}
		}
	}
	
	/**
	 * 更新操作
	 * @param sql
	 * @param params
	 * @return
	 */
	public int update(String sql, Object ... params) {
		int result = -1;
		PreparedStatement pstmt = null;
		Connection con = null;
		
		try {
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			setParams(pstmt, params);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(null, pstmt, con);
		}
		return result;
	}
	
	/**
	 * 更新操作
	 * @param sql
	 * @param params
	 * @return
	 */
	public int update(String sql, List<Object> params) {
		if (params == null || params.isEmpty()) {
			return this.update(sql);
		}
		return this.update(sql, params.toArray());
	}
	
	/**
	 * 获取结果集中列的列名
	 * @param rs
	 * @return
	 */
	private String[] getColumnNames(ResultSet rs) {
		String[] columnNames = null;
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount(); // 获取结果集中列的数量
			columnNames = new String[columnCount];
			for (int i = 0; i < columnCount; ++ i) {
				columnNames[i] = rsmd.getColumnName(i + 1).toLowerCase();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return columnNames;
	}
	
	/**
	 * 查一行数据
	 * @param sql
	 * @param params
	 * @return
	 */
	public Map<String, String> findSingle(String sql, Object ... params) {
		Map<String, String> map = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			setParams(pstmt, params);
			rs = pstmt.executeQuery();
			
			String[] columnNames = this.getColumnNames(rs);
			if (columnNames == null) {
				return Collections.emptyMap();
			}
			
			if (rs.next()) {
				map = new HashMap<String, String>();
				for (String colName : columnNames) {
					map.put(colName, rs.getString(colName));
				}
			}
			return map;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(rs, pstmt, con);
		}
		return Collections.emptyMap();
	}
	
	public Map<String, String> findSingle(String sql, List<Object> params) {
		if (params == null || params.isEmpty()) {
			return this.findSingle(sql);
		}
		return this.findSingle(sql, params.toArray());
	}
	
	public List<Map<String, String>> findMutile(String sql, Object ... params) {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		Map<String, String> map = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			setParams(pstmt, params);
			rs = pstmt.executeQuery();
			
			String[] columnNames = this.getColumnNames(rs);
			if (columnNames == null) {
				return list;
			}
			
			while (rs.next()) {
				map = new HashMap<String, String>();
				for (String colName : columnNames) {
					map.put(colName, rs.getString(colName));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(rs, pstmt, con);
		}
		return list;
	}
	
	public List<Map<String, String>> findMutile(String sql, List<Object> params) {
		if (params == null || params.isEmpty()) {
			return this.findMutile(sql);
		}
		return this.findMutile(sql, params.toArray());
	}
	
	/**
	 * 获取指定类中的所有setter方法
	 * @param cls
	 * @return
	 */
	private Map<String, Method> getSetters(Class<?> cls) {
		Map<String, Method> setters = new HashMap<String, Method>();
		Method[] methods = cls.getDeclaredMethods(); // 获取类中的所有方法
		if (methods == null || methods.length <= 0) {
			return setters;
		}
		
		String methodName = null;
		for (Method md : methods) {
			methodName = md.getName(); // 获取方法的方法名称
			if (methodName.startsWith("set")) {
				setters.put(methodName, md);
			}
		}
		return setters;
	}
	
	/**
	 * 基于对象的查询
	 * @param <T>
	 * @param cls
	 * @param sql
	 * @param params
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> T find(Class<?> cls, String sql, Object ... params) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		T obj = null;
		
		try {
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			setParams(pstmt, params);
			rs = pstmt.executeQuery();
			String[] columnNames = this.getColumnNames(rs);
			
			String val = null; // 列的值
			Class<?>[] types = null;
			Class<?> paramType = null; // 形参类型
			Map<String, Method> setters = this.getSetters(cls);
			if (setters == null || setters.isEmpty()) {
				return obj;
			}
			
			String methodName = null;
			Method method = null;
			if (rs.next()) {
				obj = (T) cls.newInstance(); // 实例化一个对象
				
				for (String columnName : columnNames) { // 循环取出每一个类的值
					val = rs.getString(columnName);
					
					if (val == null) { // 说明这一列没有值，则可以不注入
						continue;
					}
					
					methodName = "set" + columnName.substring(0, 1).toUpperCase() + columnName.substring(1);
					method = setters.get(methodName); // 根据名称获取对应的setter方法
					if (method == null) { // 如果这个属性没有提供setter方法则无法注值
						continue;
					}
					
					types = method.getParameterTypes(); // 获取这个方法的形参列表的类型
					if (types == null || types.length <= 0) { // 说明这个setter方法没有参数，则不能注值
						continue;
					}
					paramType = types[0]; // 正规的setter方法只会带一个参数
					if (paramType == Integer.TYPE) { // int
						method.invoke(obj, Integer.parseInt(val));
					} else if (paramType == Integer.class) { // Integer
						method.invoke(obj, Integer.valueOf(val));
					} else if (paramType == Double.TYPE) { // double
						method.invoke(obj, Double.parseDouble(val));
					} else if (paramType == Double.class) { // Double
						method.invoke(obj, Double.valueOf(val));
					} else if (paramType == Long.TYPE) { // long
						method.invoke(obj, Long.parseLong(val));
					} else if (paramType == Long.class) { // Long
						method.invoke(obj, Long.valueOf(val));
					} else { // 否则都当成字符串处理
						method.invoke(obj, val);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(rs, pstmt, con);
		}
		
		return obj;
	}
	
	/**
	 * 基于对象的查询
	 * @param <T>
	 * @param cls
	 * @param sql
	 * @param params
	 * @return
	 */
	public <T> T find(Class<?> cls, String sql, List<Object> params) {
		if (params == null || params.isEmpty()) {
			return this.find(cls, sql);
		}
		return this.find(cls, sql, params.toArray());
	}
	
	
	/**
	 * 基于对象的查询
	 * @param <T>
	 * @param cls
	 * @param sql
	 * @param params
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> finds(Class<?> cls, String sql, Object ... params) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		T obj = null;
		List<T> list = new ArrayList<T>();
		try {
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			setParams(pstmt, params);
			rs = pstmt.executeQuery();
			String[] columnNames = this.getColumnNames(rs);
			
			String val = null; // 列的值
			Class<?>[] types = null;
			Class<?> paramType = null; // 形参类型
			Map<String, Method> setters = this.getSetters(cls);
			if (setters == null || setters.isEmpty()) {
				return list;
			}
			
			String methodName = null;
			Method method = null;
			while (rs.next()) {
				obj = (T) cls.newInstance(); // 实例化一个对象
				
				for (String columnName : columnNames) { // 循环取出每一个类的值
					val = rs.getString(columnName);
					
					if (val == null) { // 说明这一列没有值，则可以不注入
						continue;
					}
					
					methodName = "set" + columnName.substring(0, 1).toUpperCase() + columnName.substring(1);
					method = setters.get(methodName); // 根据名称获取对应的setter方法
					if (method == null) { // 如果这个属性没有提供setter方法则无法注值
						continue;
					}
					
					types = method.getParameterTypes(); // 获取这个方法的形参列表的类型
					if (types == null || types.length <= 0) { // 说明这个setter方法没有参数，则不能注值
						continue;
					}
					paramType = types[0]; // 正规的setter方法只会带一个参数
					if (paramType == Integer.TYPE) { // int
						method.invoke(obj, Integer.parseInt(val));
					} else if (paramType == Integer.class) { // Integer
						method.invoke(obj, Integer.valueOf(val));
					} else if (paramType == Double.TYPE) { // double
						method.invoke(obj, Double.parseDouble(val));
					} else if (paramType == Double.class) { // Double
						method.invoke(obj, Double.valueOf(val));
					} else if (paramType == Long.TYPE) { // long
						method.invoke(obj, Long.parseLong(val));
					} else if (paramType == Long.class) { // Long
						method.invoke(obj, Long.valueOf(val));
					} else { // 否则都当成字符串处理
						method.invoke(obj, val);
					}
				}
				list.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(rs, pstmt, con);
		}
		
		return list;
	}
	
	public <T> List<T> finds(Class<?> cls, String sql, List<Object> params) {
		if (params == null || params.isEmpty()) {
			return this.finds(cls, sql);
		}
		return this.finds(cls, sql, params.toArray());
	}
	
	public String findStr(String sql, Object ... params) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			setParams(pstmt, params);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(rs, pstmt, con);
		}
		return "";
	}
	
	public String findStr(String sql, List<Object> params) {
		if (params == null || params.isEmpty()) {
			return this.findStr(sql);
		}
		return this.findStr(sql, params.toArray());
	}
}
